package cn.chendd.example.jpa.user.repository;

import cn.chendd.example.jpa.base.BaseRepository;
import cn.chendd.example.jpa.user.entity.User;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * 根据参数构造动态查询条件
 *
 * @author chendd
 * @date 2020/5/1 23:44
 */
public interface UserQueryDynamicParamRepository extends BaseRepository<User , String> {

    /**
     * 常规动态参数实现：出现多少次 ? 则需要定义多少个方法参数
     */
    @Query(value = "select a.* from user a where 1=1 " +
            "and (? is null or a.name = ?) " +
            "and (? is null or a.email = ?) " , nativeQuery = true)
    List<User> queryUsersByNameAndEmailGeneral(String nameFlag , String name ,
                                               String emailFlag , String email);

    /**
     * 常规动态参数实现：使用 ?数字 的参数类型
     */
    @Query(value = "select a.* from user a where 1=1 " +
            "and (?1 is null or a.name = ?1) " +
            "and (?2 is null or a.email = ?2) " , nativeQuery = true)
    List<User> queryUsersByNameAndEmailGeneral(String name , String email);

    /**
     * 涉及 in 条件的动态查询
     */
    @Query(value = "select a.* from user a where 1=1 " +
            "and if(?1 is not null , a.name = ?1 , 1=1) " +
            "and if(?2 is not null , a.email in (?3) , 1=1) " , nativeQuery = true)
    List<User> queryUsersByNameAndEmailList(String name , String emailValue , List<String> emails);

    /**
     * 涉及 in 条件的动态查询
     */
    @Query(value = "select a.* from user a where 1=1 " +
            "and if(:name is not null , a.name = :name , 1=1) " +
            "and if(:emailValue is not null , a.email in (:emails) , 1=1) " , nativeQuery = true)
    List<User> queryUsersByNameAndEmailArray(@Param("name") String name ,
                                             @Param("emailValue") String emailValue ,
                                             @Param("emails") String[] emails);

}
